# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter


class Scrapy06ReadbookPipeline:

    # 在爬虫文件执行完之前执行的方法
    def open_spider(self, spider):
        print('+++++++++++前前前前++++++++++++++++++++++++')
        self.fp = open('book.json', 'w', encoding='utf-8')  # 1.文件操作

    def process_item(self, item, spider):
        self.fp.write(str(item))
        return item

    # 在爬虫文件执行完之后执行的方法
    def close_spider(self, spider):
        self.fp.close()  # 3.文件操作
        print('-----------后后后后后-------------------------')


from scrapy.utils.project import get_project_settings
import pymysql


class MysqlPipelines:

    # 在爬虫文件执行完之前执行的方法
    def open_spider(self, spider):

        settings = get_project_settings()

        self.host = settings['DB_HOST']
        self.port = settings['DB_PORT']
        self.user = settings['DB_USER']
        self.password = settings['DB_PASSWORD']
        self.name = settings['DB_NAME']
        self.charset = settings['DB_CHARSET']
        print('-----------开启MySQL连接-------------------------')

        # 连接数据库
        self.connect()

    def connect(self):
        # 创建数据库连接
        self.conn = pymysql.connect(
            host=self.host,
            port=self.port,
            user=self.user,
            password=self.password,
            db=self.name,
            charset=self.charset
        )
        self.cursor = self.conn.cursor()

    def process_item(self, item, spider):

        sql_insert = 'INSERT INTO book (name, src) VALUES ("{}", "{}")'.format(item['name'], item['src'])

        # 执行sql
        self.cursor.execute(sql_insert)
        # 提交
        self.conn.commit()

        return item

    # 在爬虫文件执行完之后执行的方法
    def close_spider(self, spider):
        print('-----------关闭MySQL连接-------------------------')
        self.cursor.close()
        self.conn.close()
